package com.fau.wm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Hibernate;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.wm.entity.MedicalAdmin;
import com.fau.wm.entity.MedicalInStock;
import com.fau.wm.entity.MedicalKeyValue;
import com.fau.wm.entity.MedicalMedicine;
import com.fau.wm.entity.MedicalRecall;
import com.fau.wm.entity.MedicalRecallDetail;
import com.fau.wm.entity.MedicalWarehouse;
import com.fau.wm.service.IRecallManagerService;
import com.fau.wm.util.ExcelUtil;
import com.fau.wm.util.Page;
import com.fau.wm.util.ParamUtil;
import com.fau.wm.util.SystemConfig;
import com.fau.wm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class RecallManagerService extends BaseService implements
		IRecallManagerService {

	@Override
	public Page findRecallByPage(MedicalRecall recall, Map<String, Object> map) {
		StringBuilder statusSql = new StringBuilder("from MedicalRecall a where a.hospitalId = ? and a.status in (?,?,?) ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalRecall a where a.hospitalId = ? and a.status in (?,?,?) ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(recall.getHospitalId());
		objectList.add(SystemConfig.RECALL_STATUS_APPROVAL);
		objectList.add(SystemConfig.RECALL_STATUS_REJECT);
		objectList.add(SystemConfig.RECALL_STATUS_NEW);
		
		if(StringUtils.isNotBlank(recall.getTitle())){
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%"+recall.getTitle()+"%");
		}
		
		if(StringUtils.isNotBlank(recall.getSerialNum())){
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(recall.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(recall.getWarehouseName())){
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name like ? ");
			objectList.add("%"+recall.getWarehouseName()+"%");
		}
		
		//排序
		String[] sortField =(String[]) map.get("sortField");
		String[] sortOrder =(String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalRecall> recallList = new ArrayList<MedicalRecall>();
		for(Object obj : page.getResult()){
			recall = (MedicalRecall)obj;
			MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(recall.getHospitalId(), recall.getType());
			recall.setTypeVal(keyVal.getKeyValue());
			recallList.add(recall);
		}
		page.setResult(recallList);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveRecall(MedicalRecall recall, MedicalAdmin writeUser) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(recall);
		if(errorInfo.size()>0){
			return false;
		}else{
			MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, recall.getWarehouseId());
			recall.setHospitalId(writeUser.getEnterpriseId());
			recall.setWarehouse(warehouse);
			recall.setWriteUser(writeUser);
			return this.iBaseDao.saveObject(recall);
		}
	}
	
	@Override
	@Transactional
	public Boolean verSerialNum(String serialNum, String hospitalId){
		MedicalRecall recall = (MedicalRecall)this.iBaseDao.getObjectByHql("from MedicalRecall a where a.serialNum=? and hospitalId = ?", serialNum, hospitalId);
		if(null == recall){
			return true;
		}else{
			return false;
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delRecall(MedicalRecall recall) {
		String[] ids = recall.getId().split("-");
		for (String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalRecallDetail a where a.recall.id=? ", id);
			this.iBaseDao.updateHql("delete from MedicalRecall a where a.id=? ", id);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEditRecall(MedicalRecall recall) {
		MedicalRecall bean = (MedicalRecall) this.iBaseDao.getObject(MedicalRecall.class, recall.getId());
		MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, recall.getWarehouseId());
		bean.setWarehouse(warehouse);
		bean.setSerialNum(recall.getSerialNum());
		bean.setTitle(recall.getTitle());
		bean.setRecallTime(recall.getRecallTime());
		bean.setType(recall.getType());
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalRecallDetail> findRecallDetailList(String recallId) {
		List<MedicalRecallDetail> list = this.iBaseDao.getBySql("from MedicalRecallDetail a where a.recall.id=? ", null, recallId);
		for (MedicalRecallDetail medicalRecallDetail : list) {
			MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), medicalRecallDetail.getMedicine().getUnit());
			medicalRecallDetail.getMedicine().setUnitValue(kv.getKeyValue());
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Page findMedicineRecallDetailList(String medicineId,String warehouseId,Map<String, Object> map) {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalRecallDetail a where a.medicine.id=? and a.recall.warehouse.id=? and a.recall.status=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalRecallDetail a where a.medicine.id=? and a.recall.warehouse.id=? and a.recall.status=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(medicineId);
		objectList.add(warehouseId);
		objectList.add(SystemConfig.RECALL_STATUS_APPROVAL);
		
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalRecallDetail> list = (List<MedicalRecallDetail>) page.getResult();
		for (MedicalRecallDetail medicalRecallDetail : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), medicalRecallDetail.getMedicine().getUnit());
			medicalRecallDetail.getMedicine().setUnitValue(kv.getKeyValue());
		}
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveRecallDetail(MedicalRecallDetail recallDetail) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(recallDetail);
		if(errorInfo.size()>0){
			return false;
		}else{
			MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, recallDetail.getMedicineId());
			recallDetail.setMedicine(medicine);
			MedicalRecall recall = (MedicalRecall) this.iBaseDao.getObject(MedicalRecall.class, recallDetail.getRecallId());
			recallDetail.setRecall(recall);
			return this.iBaseDao.saveObject(recallDetail);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEditRecallDetail(MedicalRecallDetail recallDetail) {
		MedicalRecallDetail bean = (MedicalRecallDetail) this.iBaseDao.getObject(MedicalRecallDetail.class, recallDetail.getId());
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, recallDetail.getMedicineId());
		bean.setMedicine(medicine);
		bean.setQuantity(recallDetail.getQuantity());
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delRecallDetail(MedicalRecallDetail recallDetail) {
		this.iBaseDao.deleteObject(recallDetail);
	}

	@Override
	public Boolean checkMedicineExist(String recallId, String medicineId) {
		MedicalRecallDetail recallDetail = (MedicalRecallDetail) this.iBaseDao.getObjectByHql("from MedicalRecallDetail a where a.recall.id=? and a.medicine.id=? ", recallId, medicineId);
		if(null == recallDetail){
			return true;
		}else{
			return false;
		}
	}
	
	@Override
	public Boolean checkMedicineExistE(String recallId, String medicineId) {
		int count = this.iBaseDao.getIntCount("select count(*) from MedicalRecallDetail a where a.recall.id=? and a.medicine.id=? ", recallId, medicineId);
		if(count < 2){
			return true;
		}else{
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateRecallStatus(String status, String id) {
		MedicalRecall recall = (MedicalRecall) this.iBaseDao.getObject(MedicalRecall.class, id);
		recall.setStatus(status);
	}

	@Override
	public Page findRecallAuditByPage(Map<String, Object> map,
			MedicalRecall recall) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalRecall a where a.hospitalId = ? ");
		StringBuilder statusSql = new StringBuilder("from MedicalRecall a where a.hospitalId = ? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(recall.getHospitalId());
		
		if(StringUtils.isNotBlank(recall.getTitle())){
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%"+recall.getTitle()+"%");
		}
		
		if(StringUtils.isNotBlank(recall.getSerialNum())){
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(recall.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(recall.getWarehouseName())){
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name like ? ");
			objectList.add("%"+recall.getWarehouseName()+"%");
		}
		
		countSql.append("and a.status=? ");
		statusSql.append("and a.status=? ");
		objectList.add(SystemConfig.RECALL_STATUS_PENDING);
		
		//排序
		String[] sortField =(String[]) map.get("sortField");
		String[] sortOrder =(String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalRecall> recallList = new ArrayList<MedicalRecall>();
		for(Object obj : page.getResult()){
			recall = (MedicalRecall) obj;
			MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(recall.getHospitalId(), recall.getType());
			recall.setTypeVal(keyVal.getKeyValue());
			recallList.add(recall);
		}
		page.setResult(recallList);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateAuditUser(MedicalAdmin auditUser, String recallId) {
		MedicalRecall recall = (MedicalRecall) this.iBaseDao.getObject(MedicalRecall.class, recallId);
		recall.setAuditUser(auditUser);
	}

	@Override
	public MedicalRecallDetail findRecallDetailById(String id) {
		MedicalRecallDetail recallDetail = (MedicalRecallDetail) this.iBaseDao.getObject(MedicalRecallDetail.class, id);
		Hibernate.initialize(recallDetail.getRecall());
		return recallDetail;
	}
	
	@SuppressWarnings({ "unchecked" })
	@Override
	public Workbook exportRecallData(String id) throws Exception{
		MedicalRecall bean = (MedicalRecall) this.iBaseDao.getObject(MedicalRecall.class, id);
		List<MedicalRecallDetail> medicineDetailList=this.iBaseDao.getBySql("from MedicalRecallDetail where recall.id=?" , null, bean.getId());
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		String realPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_RECALL);
		File file = new File(realPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		
		Cell cell=null;
		
		Sheet sheet = workbook.getSheetAt(0);
		cell = sheet.getRow(1).getCell(1);
		cell.setCellValue(bean.getSerialNum());
		cell = sheet.getRow(1).getCell(3);
		cell.setCellValue(bean.getTitle());
		cell = sheet.getRow(1).getCell(6);
		cell.setCellValue(bean.getWarehouse().getName());
		cell = sheet.getRow(1).getCell(8);
		MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(user.getEnterpriseId(), bean.getType());
		cell.setCellValue(keyVal.getKeyValue());
		cell = sheet.getRow(2).getCell(1);
		cell.setCellValue(DateFormatUtils.format(bean.getCreateTime(),"yyyy-MM-dd"));
		cell= sheet.getRow(2).getCell(5);
		cell.setCellValue(bean.getWriteUser().getName());
		
		for(int i=0;i<medicineDetailList.size();i++){
			String name=medicineDetailList.get(i).getMedicine().getName();
			cell=sheet.getRow(4).getCell(0);
			cell.setCellValue(name);
			Integer quantity=medicineDetailList.get(i).getQuantity();
			cell=sheet.getRow(4+i).getCell(5);
			cell.setCellValue(quantity);
		}
		
		return workbook;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importRecallData(File file, MedicalAdmin writeUser)
			throws Exception {
		String errorInfo = "";
		
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		
		Row row = sheet.getRow(1);
		
		String serialNum = null;
		if(HSSFCell.CELL_TYPE_STRING == row.getCell(1).getCellType()){
			serialNum = row.getCell(1).getStringCellValue().trim();
		}else{
			serialNum = String.format("%.0f", row.getCell(1).getNumericCellValue());
		}
		
		MedicalRecall medicalRecall = (MedicalRecall) this.iBaseDao.getObjectByHql("from MedicalRecall a where a.serialNum=? ", serialNum);
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		if(null == medicalRecall) {
			medicalRecall = new MedicalRecall();
			medicalRecall.setSerialNum(serialNum);
			medicalRecall.setTitle(row.getCell(3).getStringCellValue().trim());
			MedicalWarehouse warehouse=(MedicalWarehouse) this.iBaseDao.getObjectByHql("from MedicalWarehouse a where a.name=? and a.hospitalId=? ", row.getCell(6).getStringCellValue().trim(),user.getEnterpriseId());
			medicalRecall.setWarehouse(warehouse);
			MedicalKeyValue  keyVal = (MedicalKeyValue )this.iBaseDao.getObjectByHql("from MedicalKeyValue a where a.keyValue = ? and a.hospitalId=? ",row.getCell(8).getStringCellValue().trim(),user.getEnterpriseId());
			medicalRecall.setType(keyVal.getKeyName());
			row = sheet.getRow(2);
			medicalRecall.setRecallTime(DateUtils.parseDate(row.getCell(1).getStringCellValue().trim(),"yyyy-MM-dd"));
			medicalRecall.setWriteUser(writeUser);
			
			medicalRecall.setHospitalId(writeUser.getEnterpriseId());
			this.iBaseDao.saveObject(medicalRecall);
			
			while(rows.hasNext()) {
				row = rows.next();
				MedicalRecallDetail recallDetail=new MedicalRecallDetail();
				if(row.getRowNum() == 0||row.getRowNum() == 1||row.getRowNum() == 2|| row.getRowNum() == 3) continue;
				if(StringUtils.isNotBlank(row.getCell(0).getStringCellValue().trim())){
					MedicalMedicine medicine=(MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine a where a. name=? ", row.getCell(0).getStringCellValue().trim());
					recallDetail.setMedicine(medicine);
					if(row.getCell(5)!=null){
				          row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
				          recallDetail.setQuantity(Integer.parseInt(row.getCell(5).getStringCellValue().trim()));				     
				    }
					recallDetail.setRecall(medicalRecall);
					this.iBaseDao.saveObject(recallDetail);
				}else{
					break;
				}
			}
		}else{
			errorInfo = SystemConfig.IMPORT_RECALL_SERIALNUM;
		}
		if(file.isFile() && file.canWrite()){
			file.delete();
		}
		return errorInfo;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean toAudit(String sid) {
		List<MedicalRecallDetail> list = this.iBaseDao.getBySql("from MedicalRecallDetail a where a.recall.id=? ", null, sid);
		if(list.size() < 1){
			return false;
		}else{
			this.iBaseDao.updateHql("update MedicalRecall a set a.status=? where a.id=? ", SystemConfig.RECALL_STATUS_PENDING,sid);
			return true;
		}
	}

	@Override
	public String validProExist(MedicalRecallDetail recallDetail) {
		StringBuilder errorInfo = new StringBuilder("");
		if(recallDetail.getId().indexOf(",") != -1) {
			String[] detailIds = recallDetail.getId().split(",");
			for(String dId : detailIds) {
				MedicalRecallDetail bean = (MedicalRecallDetail) this.iBaseDao.getObject(MedicalRecallDetail.class, dId);
				MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", recallDetail.getWarehouseId(), bean.getMedicine().getId());
				if(null != inStock && inStock.getQuantity() > bean.getQuantity()) {
					continue;
				} else {
					errorInfo.append(bean.getMedicine().getName()+"、");
				}
			}
			if(StringUtils.isNotBlank(errorInfo.toString())) {
				errorInfo.deleteCharAt(errorInfo.lastIndexOf("、"));
			} else {
				errorInfo.append("true");
			}
		} else {
			MedicalRecallDetail bean = (MedicalRecallDetail) this.iBaseDao.getObject(MedicalRecallDetail.class, recallDetail.getId());
			MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", recallDetail.getWarehouseId(), bean.getMedicine().getId());
			if(null != inStock && inStock.getQuantity() > bean.getQuantity()) {
				errorInfo.append("true");
			} else {
				errorInfo.append(bean.getMedicine().getName());
			}
		}
		return errorInfo.toString();
	}
	
	@SuppressWarnings({ "unchecked" })
	@Override
	public Workbook downloadRecallTemp(MedicalAdmin admin) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.MEDICINE_RECALL_MASTERPLATE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		//仓库类型
		List<MedicalKeyValue> keyValList = this.iBaseDao.findValueByType(admin.getEnterpriseId(), SystemConfig.WM_SYSTEM_TYPE, SystemConfig.RECALL_TYPE);
		List<String> medicalRecallList = new ArrayList<String>();
		for(MedicalKeyValue medicalKeyValue : keyValList){
			medicalRecallList.add(medicalKeyValue.getKeyValue());
		}
		String[] medicalRecallTypes = medicalRecallList.toArray(new String[keyValList.size()]);
		
		// 仓库
		List<MedicalWarehouse> mewarehouseList = this.iBaseDao.getBySql("from MedicalWarehouse m where m.hospitalId=? ", null,  admin.getEnterpriseId());
		List<String> medicalWarehouseList = new ArrayList<String>();
		for(MedicalWarehouse medicalWarehouse : mewarehouseList){
			medicalWarehouseList.add(medicalWarehouse.getName());
		}
		String[] medicalWarehouse = medicalWarehouseList.toArray(new String[mewarehouseList.size()]);
		
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列   
		sheet = ExcelUtil.setPullDown(sheet, medicalWarehouse, 1, 500, 6, 6);	// 第一列的前501行都设置为选择列表形式.
		sheet = ExcelUtil.setPullDown(sheet, medicalRecallTypes, 1, 500, 8, 8);
		return workbook;
	}
}
